* Replication code for Hirst & Robertson (2022)
* File 2 of 4 
* This file was written for Stata 16.1 and has not been tested on any other version.
* Please read the README file before continuing. 

clear all
cd "C:\your\data\directory\" //Change this to where you are storing the files
local date FINAL


/*RAW DATA IN -- FIS Securities Lending Data from Quandl 
The following must be in the "SecLending" subdirectory of the data directory,
or the file locations must be changed manually in the code:
"SecLending\FIS_R1_8cf8003ff628b4b950a51081e2286e02.csv"
"SecLending\FIS_U2_1cdee900219d51dea5edecc4bd325fe1.csv"
"SecLending\FIS_POS_ba29ca06651602bd49fb81b3a9a947a4.csv"
"SecLending\SLDPOS_`year'Q`Q'.csv" [year = 2014 / 2017 ; Q = 1 / 4]
*/

/*INTERMEDIATE DATA IN 
"cusips_for_FIS" //from 01_PrepareRecordDate 
"MeetingLevel" //from 01_PrepareRecordDate 
*/

/* Clean up Lending Data */
if 0 == 1 {
	*Make the GIGANTIC R1 file manageable*
	if 1 == 1 { 
		import delimited "SecLending\FIS_R1_8cf8003ff628b4b950a51081e2286e02.csv", clear    
		drop isin contracttypeid collateraltypeid collateralcurrencyid tradingsymbol
		gen temp = date(date, "YMD")
		format temp %td
		drop date
		rename temp date
		gen year = year(date)
		gen cusip8 = substr(cusip, 1, 8)
		drop if cusip == ""
		egen rate = rowmean(lowerbound upperbound)
		drop lowerbound upperbound
		merge m:1 cusip8 using "cusips_for_FIS"
		drop if _merge == 1  //This drops almost 70 million observations. Much easier to work with!
		/*
		. distinct cusip8 if _merge == 2

				|        Observations
				|      total   distinct
		--------+----------------------
		 cusip8 |       1410       1410

		. distinct cusip8 if _merge == 3

				|        Observations
				|      total   distinct
		--------+----------------------
		 cusip8 |    7434375       5793

		*/
		drop if _merge == 2
		/*
		. duplicates r cusip8 date

		Duplicates in terms of cusip8 date

		--------------------------------------
		   copies | observations       surplus
		----------+---------------------------
				1 |      7434375             0 // So we have 1 observations per cusip8 x date
		--------------------------------------

		. duplicates r cusip6 date

		Duplicates in terms of cusip6 date

		--------------------------------------
		   copies | observations       surplus
		----------+---------------------------
				1 |      7434375             0 // Also 1 observations per cusip6 x date -- great!
		--------------------------------------
		*/
		drop _merge
		save "Raw/SecLendingR1_all", replace // This file has daily lending data 
		}
	*Make the GIGANTIC U2 file manageable*
	if 1 == 1 { 
		import delimited "SecLending\FIS_U2_1cdee900219d51dea5edecc4bd325fe1.csv", clear    
		drop isin tradingsymbol
		drop if cusip == ""
		gen temp = date(date, "YMD")
		format temp %td
		drop date
		rename temp date
		gen year = year(date)
		gen cusip8 = substr(cusip, 1, 8)
		drop cusip
		merge m:1 cusip8 using "cusips_for_FIS"
		drop if _merge == 1  //This drops over 90 million observations. Much easier to work with!
		/*
		. distinct cusip8 if _merge == 2

				|        Observations
				|      total   distinct
		--------+----------------------
		 cusip8 |       1693       1693

		. distinct cusip8 if _merge == 3

				|        Observations
				|      total   distinct
		--------+----------------------
		 cusip8 |    6599354       5510
		*/
		drop if _merge == 2
		/*
		. duplicates r cusip8 date

		Duplicates in terms of cusip8 date

		--------------------------------------
		   copies | observations       surplus
		----------+---------------------------
				1 |      6599354             0 // 1 observations per cusip8 x date -- great!
		--------------------------------------

		. duplicates r cusip6 date

		Duplicates in terms of cusip6 date

		--------------------------------------
		   copies | observations       surplus
		----------+---------------------------
				1 |      7434375             0 // Also 1 observations per cusip6 x date -- great!
		--------------------------------------
		*/
		drop _merge
		gen calc_lent = utilizationpercentunits/100*availableunits
		save "Raw/SecLendingU2_all", replace // This file has daily lending data 
		}
	*Make the GIGANTIC POS files manageable*
	if 1 == 1 { 
		*First File*
		if 1 == 1 {
			import delimited "SecLending\FIS_POS_ba29ca06651602bd49fb81b3a9a947a4.csv", clear    
			drop isin tradingsymbol
			drop if cusip == ""
			keep if contracttypeid == "A"
			keep if loanstageid == "A"
			keep if collateraltypeid == "A"
			drop contracttypeid loanstageid collateraltypeid retailloanrateavg retailloanratemax retailloanratemin retailloanratestdev collateralcurrencyid

			gen temp = date(date, "YMD")
			format temp %td
			drop date
			rename temp date
			gen year = year(date)
			gen cusip8 = substr(cusip, 1, 8)
			duplicates r cusip8 date
			/*
			. duplicates r cusip8 date

			Duplicates in terms of cusip8 date

			--------------------------------------
			   copies | observations       surplus
			----------+---------------------------
					1 |     34372287             0 //No duplicates!
			--------------------------------------
			*/
			drop cusip
			save "Raw\SecLending\FIS_POS_short", replace
			
			merge m:1 cusip8 using "cusips_for_FIS"
			drop if _merge == 1  //This drops over 31 million observations. Much easier to work with!
			/*
			. distinct cusip8 if _merge == 2

					|        Observations
					|      total   distinct
			--------+----------------------
			 cusip8 |       2448       2448



			. distinct cusip8 if _merge == 3

					|        Observations
					|      total   distinct
			--------+----------------------
			 cusip8 |    3088713       4755
			*/
			drop if _merge == 2
			/*
			. duplicates r cusip8 date

			Duplicates in terms of cusip8 date

			--------------------------------------
			   copies | observations       surplus
			----------+---------------------------
					1 |      3088713             0 // 1 observations per cusip8 x date -- great!
			--------------------------------------

			. duplicates r cusip6 date

			Duplicates in terms of cusip6 date

			--------------------------------------
			   copies | observations       surplus
			----------+---------------------------
					1 |      3088713             0 // Also 1 observations per cusip6 x date -- great!
			--------------------------------------
			*/
			save "Raw/SecLendingPOS_2018", replace // This file has daily lending data 
			}
		*The 2014-2018 Files* 
		if 1 == 1 {
			forvalues year = 2014 / 2017 {
				forvalues Q = 1 /4 {
					import delimited "SecLending\SLDPOS_`year'Q`Q'.csv", clear    
					drop isin tradingsymbol
					drop if cusip == ""
					keep if contracttypeid == "A"
					keep if loanstageid == "A"
					keep if collateraltypeid == "A"

					drop contracttypeid loanstageid collateraltypeid retailloanrateavg retailloanratemax retailloanratemin retailloanratestdev collateralcurrencyid
					gen temp1 = substr(date, 1,10)
					gen temp = date(temp1, "YMD")
					format temp %td
					drop date temp1
					rename temp date
					gen year = year(date)
					qui sum year 
					assert r(max) == `year'
					assert r(min) == `year'
					gen quarter = quarter(date)
					qui sum quarter
					assert r(max) == `Q'
					assert r(min) == `Q'
					gen cusip8 = substr(cusip, 1, 8)
					duplicates t cusip8 date, gen(dup)
					collapse (sum) units (sum) marketvalueusd (sum) tickets (mean) age (mean) dup, by(date year cusip8)
					duplicates r cusip8 date
					assert r(N) == r(unique_value)
					
					save "Raw\SecLending\FIS_POS_`year'Q`Q'_short", replace
					
					merge m:1 cusip8 using "cusips_for_FIS"
					drop if _merge == 1  
					drop if _merge == 2
					duplicates r cusip8 date
					assert r(N) == r(unique_value)
					save "Raw/SecLendingPOS_`year'Q`Q'", replace // This file has daily lending data 
					}
				}
			}		
		use "Raw/SecLendingPOS_2018", clear 
		forvalues year = 2014 / 2017 {
			forvalues Q = 1 /4 {
				append using "Raw/SecLendingPOS_`year'Q`Q'"
				}
			}
		duplicates r cusip8 date
		assert r(N) == r(unique_value)
		distinct cusip8 
		drop _merge dup
		save "Raw/SecLendingPOS_all", replace // This file has daily lending data 
		}
	
	*Now foreach cusip6, grab the record dates (available)*
	if 1 == 1 {
		use "MeetingLevel", clear
		drop if SHRCD_1 != 1
		egen first_filed = min(date), by(MeetingID)
		format first_filed %td
		drop formtype datefiled filename date prelim_proxy def_proxy proxy before_record
		bysort MeetingID : keep if _n == 1
		drop N year tic cusip6 
		
		*Strategy for mapping a meeting to lending data is the same as mapping to filings: we assign each MeetingID within a firm an identifier. Then we iterate over these identifiers. Each time, we merge on cusip6 to LENDING data, and keep all lending observations that occured within 180 days of the record date. Later, we append these files back together.  

	bysort cusip8: gen N = _n 
		qui sum N 
		local max = r(max)
	
	forvalues i = 1 / `max' {
		preserve 
			keep if N == `i'
			merge 1:m cusip8 using "Raw/SecLendingU2_all"
			drop if _merge == 2 
			keep if date >= Recorddate - 180 & date <= Recorddate + 180
			tempfile temp`i'
			save `temp`i'', replace
			restore
		}
		clear 
		forvalues i = 1 / `max' {
			append using `temp`i'' 
			}
		drop N _merge
		drop if MeetingID == .
			//Create trading days 
		bcal create lending, from(date) replace
		gen trade_date = bofd("lending", date)
		gen trade_Recorddate = bofd("lending", Recorddate)
		gen trade_MeetingDate = bofd("lending", MeetingDate)
		gen trade_first_filed = bofd("lending", first_filed)
		format trade_date trade_Recorddate trade_MeetingDate trade_first_filed %tblending

		save "SecLendingU2_dates", replace
		}
	*Now foreach cusip6, grab the record dates -  POS file*
	if 1 == 1 {
		use "MeetingLevel", clear
		drop if SHRCD_1 != 1
		egen first_filed = min(date), by(MeetingID)
		format first_filed %td
		drop formtype datefiled filename date prelim_proxy def_proxy proxy before_record
		bysort MeetingID : keep if _n == 1
		drop N year tic cusip6 
		
		*Strategy for mapping a meeting to lending data is the same as mapping to filings: we assign each MeetingID within a firm an identifier. Then we iterate over these identifiers. Each time, we merge on cusip6 to LENDING data, and keep all lending observations that occured within 180 days of the record date. Later, we append these files back together.  

	bysort cusip8: gen N = _n 
		qui sum N 
		local max = r(max)
	
	forvalues i = 1 / `max' {
		preserve 
			keep if N == `i'
			merge 1:m cusip8 using "Raw/SecLendingPOS_all"
			drop if _merge == 2 
			keep if date >= Recorddate - 180 & date <= Recorddate + 180
			tempfile temp`i'
			save `temp`i'', replace
			restore
		}
		clear 
		forvalues i = 1 / `max' {
			append using `temp`i'' 
			}
		drop N _merge
		drop if MeetingID == .
			//Create trading days 
		bcal create lending, from(date) replace
		gen trade_date = bofd("lending", date)
		gen trade_Recorddate = bofd("lending", Recorddate)
		gen trade_MeetingDate = bofd("lending", MeetingDate)
		gen trade_first_filed = bofd("lending", first_filed)
		format trade_date trade_Recorddate trade_MeetingDate trade_first_filed %tblending

		save "SecLendingPOS_dates", replace
		}

	*Construct percent changes in available around the record date*
	if 1 == 1 {
		use "SecLendingU2_dates", clear
		gen event_time = date - Recorddate 
		gen trade_event_time = trade_date - trade_Recorddate 
		xtset MeetingID trade_date
		rename availableunits available
		gen gr_available = available / L.available  
		label var gr_available "Percent change in available"
		winsor2 gr_available, by(trade_event_time) cuts(1 99) suff(_w1)
		winsor2 gr_available, by(trade_event_time) cuts(2 98) suff(_w2)
		winsor2 gr_available, by(trade_event_time) cuts(5 95) suff(_w5)	
		foreach i in 20 15 10 5 3 1 { //now construct the percent change between i days before and the record date
			foreach var in w1 w2 w5 {
			    gen temp = ln(gr_available_`var') 
				egen temp1 = total(temp) if trade_event_time >= - `i' & trade_event_time <= 0 , by(MeetingID)
				drop temp
				gen gr_available_`var'_pre_`i' = (exp(temp1)*100 - 100) 
				drop temp1
				label var gr_available_`var'_pre_`i' "Percent change in shares available; [`i' days pre record date, record date]"
				}
			}
		foreach i in 20 15 10 5 3 1 { //now construct the percent change between the recrd date and i days after
			foreach var in w1 w2 w5 {
			    gen temp = ln(gr_available_`var') 
				egen temp1 = total(temp) if trade_event_time >= 0 & trade_event_time <= `i' , by(MeetingID)
				drop temp
				gen gr_available_`var'_post_`i' = (exp(temp1)*100 - 100) 
				drop temp1
				label var gr_available_`var'_post_`i' "Percent change in shares available; [record date, `i' days post record date]"
				}
			}
		foreach i in 5 3 1 { //now construct the number lent i TRADING days before and after the record date
			gen temp = available if trade_event_time == -`i'
			egen available_pre_`i' = mean(temp), by(MeetingID)
			drop temp
			label var available_pre_`i' "Number of shares available; `i' TRADING days pre record date"
			gen temp = available if trade_event_time == `i'
			egen available_post_`i' = mean(temp), by(MeetingID)
			drop temp
			label var available_post_`i' "Number of shares available; `i' TRADING days post record date"
			}
		collapse gr_available_* available_p*, by(MeetingID)
		
		save "U2_available", replace
		}
		
	*Construct number of share lent around the record date*
	if 1 == 1 {
		use "SecLendingPOS_dates", clear
		gen event_time = date - Recorddate 
		gen trade_event_time = trade_date - trade_Recorddate 
		xtset MeetingID trade_date
		gen gr_units = units / L.units  
		label var gr_units "Percent change in units"
		winsor2 gr_units, by(trade_event_time) cuts(1 99) suff(_w1)
		winsor2 gr_units, by(trade_event_time) cuts(2 98) suff(_w2)
		winsor2 gr_units, by(trade_event_time) cuts(5 95) suff(_w5)	
		foreach i in 20 15 10 5 3 1 { //now construct the percent change between i days before and the record date
			foreach var in w1 w2 w5 {
			    gen temp = ln(gr_units_`var') 
				egen temp1 = total(temp) if trade_event_time >= - `i' & trade_event_time <= 0 , by(MeetingID)
				drop temp
				gen gr_units_`var'_pre_`i' = (exp(temp1)*100 - 100) 
				drop temp1
				label var gr_units_`var'_pre_`i' "Percent change in shares lent; [`i' days pre record date, record date]"
				}
			}
		foreach i in 20 15 10 5 3 1 { //now construct the percent change between the recrd date and i days after
			foreach var in w1 w2 w5 {
			    gen temp = ln(gr_units_`var'/100)
				egen temp1 = total(temp) if trade_event_time >= 0 & trade_event_time <= `i' , by(MeetingID)
				drop temp
				gen gr_units_`var'_post_`i' = (exp(temp1)*100 - 100) 
				drop temp1
				label var gr_units_`var'_post_`i' "Percent change in shares lent; [record date, `i' days post record date]"
				}
			}
		foreach i in 5 3 1 { //now construct the number lent i TRADING days before and after the record date
			gen temp = units if trade_event_time == -`i'
			egen lent_pre_`i' = mean(temp), by(MeetingID)
			drop temp
			label var lent_pre_`i' "Number of shares lent; `i' TRADING days pre record date"
			gen temp = units if trade_event_time == `i'
			egen lent_post_`i' = mean(temp), by(MeetingID)
			drop temp
			label var lent_post_`i' "Number of shares lent; `i' TRADING days post record date"
			}
		collapse gr_units_* lent_p*, by(MeetingID)
		
		save "POS_units", replace
		}
	}

********************
*** Make Figures *** 
********************
if 1 == 1 {	
	graph set window fontface "Times"	
	*Start with Units (POS)*	
	if 1 == 1 {
		use "SecLendingPOS_dates", clear
		gen trade_event_time = trade_date - trade_Recorddate
		keep if trade_event_time >=-40 & trade_event_time <=40
		xtset MeetingID trade_date
		gen gr_units = (units / L.units - 1)*100
		label var gr_units "Percent change in units"
		winsor2 gr_units, by(trade_event_time) cuts(5 95) suff(_w5)	
		local var gr_units_w5
		*POOLED*
		if 1 == 1 {
			preserve 
				statsby, clear by(trade_event_time): ci mean (`var')
				label var mean "Percent" 
				label var lb "95% CI"
				label var trade_event_time "Trading Days Before / After Record Date"
				twoway (line mean trade_event_time, lcolor(black) lpattern(solid)) (line lb trade_event_time, lcolor(black) lpattern(dash)) (line ub trade_event_time, lcolor(black) lpattern(dash)) if trade_event_time >= -40 & trade_event_time <= 40, legend(off) title("Figure 2: Percent Change in Number of Shares Lent, by Day") subtitle("40 Trading Days Before / After Record Date") note("Percent change in shares lent is winsorized at 5% daily in event time." "This is averaged daily in event time." "95% confidence interval is shown in dashed lines") xlabel(-40 (10) 40) ytitle("Percent (%)")
				graph export "Results/Figure2_`date'.svg", replace fontface(Times)
				restore 
			}
		*proxy_before*
		if 1 == 1 {
			preserve 
				statsby, clear by(trade_event_time proxy_before): ci mean (`var')
				label var trade_event_time "Trading Days Before / After Record Date"
				twoway (line mean trade_event_time  if proxy_before == 0, lcolor(black) lpattern(solid)) (line lb trade_event_time  if proxy_before == 0, lcolor(black) lpattern(dash)) (line ub trade_event_time  if proxy_before == 0, lcolor(black) lpattern(dash)) (line mean trade_event_time  if proxy_before == 1, lcolor(red) lpattern(solid)) (line lb trade_event_time  if proxy_before == 1, lcolor(red) lpattern(dash)) (line ub trade_event_time  if proxy_before == 1, lcolor(red) lpattern(dash)) if trade_event_time >= -15 & trade_event_time <= 15, legend(label(1 "Hidden Agenda") label(4 "No Hidden Agenda") order(1 4)) title("Percent Change in Shares Lent") note("Percent change in shares lent is winsorized at 5% daily in event time." "This is averaged daily in event time." "95% confidence interval is shown in dashed lines") xlabel(-15 (5) 15) ytitle("Percent (%)")
				graph save Lent_proxy_before , replace
				restore 
			}
		*meeting_type*
		if 1 == 1 {
			preserve 
				gen meeting_type = .
				replace meeting_type = 1 if MeetingType == "Annual"
				replace meeting_type = 2 if MeetingType == "Proxy Contest"
				replace meeting_type = 3 if MeetingType == "Special"
			
				statsby, clear by(trade_event_time  meeting_type): ci mean (`var')
				label var trade_event_time "Trading Days Before / After Record Date"
							
				twoway (line mean trade_event_time  if meeting_type == 1, lcolor(black) lpattern(solid)) (line mean trade_event_time  if meeting_type == 2, lcolor(red) lpattern(solid)) (line mean trade_event_time  if meeting_type == 3, lcolor(blue) lpattern(solid)) if trade_event_time  >= -15 & trade_event_time <= 15, legend(label(1 "Annual") label(2 "Proxy Contest") label(3 "Special") order(1 2 3)) title("Percent Change in Shares Lent") note("Percent change in shares lent is winsorized at 5% daily in event time." "This is averaged daily in event time.") xlabel(-15 (5) 15) ytitle("Percent (%)")
				graph save Lent_meeting_type, replace
				restore 
			}
		}	

	*Now do Available (U2)*	
	if 1 == 1 {	
		use "SecLendingU2_dates", clear
		gen trade_event_time = trade_date - trade_Recorddate
		keep if trade_event_time >=-40 & trade_event_time <=40
		xtset MeetingID trade_date
		gen gr_available = (availableunits / L.availableunits - 1)*100
		label var gr_available "Percent change in units"
		winsor2 gr_available, by(trade_event_time) cuts(5 95) suff(_w5)	
		local var gr_available_w5

		*POOLED*
		if 1 == 1 {
			preserve 
				statsby, clear by(trade_event_time): ci mean (`var')
				label var mean "`var'" 
				label var lb "95% CI"
				label var trade_event_time "Trading Days Before / After Record Date"
				twoway (line mean trade_event_time, lcolor(black) lpattern(solid)) (line lb trade_event_time, lcolor(black) lpattern(dash)) (line ub trade_event_time, lcolor(black) lpattern(dash)) if trade_event_time >= -40 & trade_event_time <= 40, legend(off) title("Figure 1: Percentage Change in Number of Shares Available, by Day") subtitle("40 Trading Days Before / After Record Date") note("Percent change in shares available is winsorized at 5% daily in event time." "This is averaged daily in event time." "95% confidence interval is shown in dashed lines") xlabel(-40 (10) 40) ytitle("Percent (%)")
				graph export "Results/Figure1_`date'.svg", replace  fontface(Times)
				restore 
			}
		*proxy_before*
		if 1 == 1 {
			preserve 
				statsby, clear by(trade_event_time proxy_before): ci mean (`var')
				label var mean "`var'" 
				label var lb "95% CI"
				label var trade_event_time "Trading Days Before / After Record Date"
				twoway (line mean trade_event_time  if proxy_before == 0, lcolor(black) lpattern(solid)) (line lb trade_event_time  if proxy_before == 0, lcolor(black) lpattern(dash)) (line ub trade_event_time  if proxy_before == 0, lcolor(black) lpattern(dash)) (line mean trade_event_time  if proxy_before == 1, lcolor(red) lpattern(solid)) (line lb trade_event_time  if proxy_before == 1, lcolor(red) lpattern(dash)) (line ub trade_event_time  if proxy_before == 1, lcolor(red) lpattern(dash)) if trade_event_time  >= -15 & trade_event_time <= 15, legend(label(1 "Hidden Agenda") label(4 "No Hidden Agenda") order(1 4 )) title("Percent Change in Shares Available") note("Percent change in shares available is winsorized at 5% daily in event" "time. This is averaged daily in event time." "95% confidence interval is shown in dashed lines") xlabel(-15 (5) 15) ytitle("Percent (%)")
				graph save  Available_proxy_before, replace
				restore 
			}
		*meeting_type*
		if 1 == 1 {
			preserve 
				gen meeting_type = .
				replace meeting_type = 1 if MeetingType == "Annual"
				replace meeting_type = 2 if MeetingType == "Proxy Contest"
				replace meeting_type = 3 if MeetingType == "Special"
			
				statsby, clear by(trade_event_time  meeting_type): ci mean (`var')
				label var mean "`var'" 
				label var lb "95% CI"
				label var trade_event_time "Trading Days Before / After Record Date"
							
				twoway (line mean trade_event_time  if meeting_type == 1, lcolor(black) lpattern(solid)) (line mean trade_event_time  if meeting_type == 2, lcolor(red) lpattern(solid)) (line mean trade_event_time  if meeting_type == 3, lcolor(blue) lpattern(solid)) if trade_event_time  >= -15 & trade_event_time <= 15, legend(label(1 "Annual") label(2 "Proxy Contest") label(3 "Special") order(1 2 3)) title("Percent Change in Shares Available") note("Percent change in shares available is winsorized at 5% daily in event" "time. This is averaged daily in event time.") xlabel(-15 (5) 15) ytitle("Percent (%)")
				graph save Available_meeting_type, replace
				restore 
			}
		}	
 
	grc1leg Available_proxy_before.gph Lent_proxy_before.gph ///
		, legendfrom(Lent_proxy_before.gph) ///
		title("Figure 3: Securities Lending Behavior Around Record Dates") ///
		subtitle("By Whether There Was a Hidden Agenda")
	graph export "Results/Figure3_`date'.svg", replace  fontface(Times)
	  
	grc1leg Available_meeting_type.gph Lent_meeting_type.gph ///
		, legendfrom(Lent_meeting_type.gph) ///
		title("Figure 4: Securities Lending Behavior Around Record Dates") ///
		subtitle("By Meeting Type")
	graph export "Results/Figure4_`date'.svg", replace  fontface(Times)
	
	capture erase "Lent_proxy_before.gph"
	capture erase "Available_proxy_before.gph"
	capture erase "Lent_meeting_type.gph"
	capture erase "Available_meeting_type.gph"			   
	}
	

*********************************
*** Make Supplementary Tables ***	
*********************************
if 1 == 1 {
	*Start with Available*
	if 1 == 1 {
		use "SecLendingU2_dates", clear
		gen trade_event_time = trade_date - trade_Recorddate
		keep if trade_event_time >=-40 & trade_event_time <=40
		xtset MeetingID trade_date
		gen gr_available = (availableunits / L.availableunits - 1)*100
		label var gr_available "Percent change"
		winsor2 gr_available, by(trade_event_time) cuts(1 99) suff(_w1)
		winsor2 gr_available, by(trade_event_time) cuts(2 98) suff(_w2)
		winsor2 gr_available, by(trade_event_time) cuts(5 95) suff(_w5)	
		
		
		mat def B=J(60,7,.)
		local vars gr_available_w5 gr_available_w2 gr_available_w1  
		mat colnames B = date gr_available_w5 gr_available_w5 gr_available_w2 gr_available_w2 gr_available_w1 gr_available_w1 

		local k = 2 
		foreach var in `vars' {
			local j = 1
			forvalues i = -15/10 {
				quietly{
				    local k1 = `k'+1
					mat B[`j',1] = `i' 
					mean `var' if trade_event_time == `i' 
					local b = (_b[`var'])
					local v = sqrt(e(V)[1,1])
					mat B[`j',`k'] = round(`b',.001) 
*					local j = `j' + 1
					mat B[`j',`k1'] = round(`v',.00001) 
					local j = `j' + 1
					}
				}
			local k = `k' + 2
			}
		count if gr_available_w5 != . &  trade_event_time == 0
		mat B[`j',2] = r(N) 
		
		putexcel set "Results/SupTable2a_`date'", replace
		putexcel B1 = "Available"	
		putexcel A`j' = "N"	
		putexcel A2 = mat(B) , colnames	
		}
		
	*Now do Lent*
	if 1 == 1 {
		use "SecLendingPOS_dates", clear
		gen trade_event_time = trade_date - trade_Recorddate
		keep if trade_event_time >=-40 & trade_event_time <=40
		xtset MeetingID trade_date
		gen gr_units = (units / L.units - 1)*100
		label var gr_units "Percent change in units"
		winsor2 gr_units, by(trade_event_time) cuts(1 99) suff(_w1)
		winsor2 gr_units, by(trade_event_time) cuts(2 98) suff(_w2)
		winsor2 gr_units, by(trade_event_time) cuts(5 95) suff(_w5)	
		
		mat def B=J(60,7,.)
		local vars gr_units_w5 gr_units_w2 gr_units_w1  
		mat colnames B = date gr_units_w5 gr_units_w5 gr_units_w2 gr_units_w2 gr_units_w1 gr_units_w1  

		local k = 2 
		foreach var in `vars' {
			local j = 1
			forvalues i = -15/10 {
				quietly{
				    local k1 = `k'+1
					mat B[`j',1] = `i' 
					mean `var' if trade_event_time == `i' 
					local b = (_b[`var'])
					local v = sqrt(e(V)[1,1])
					mat B[`j',`k'] = round(`b',.001) 
*					local j = `j' + 1
					mat B[`j',`k1'] = round(`v',.00001) 
					local j = `j' + 1
					}
				}
			local k = `k' + 2
			}
		count if gr_units_w5 != . &  trade_event_time == 0
		mat B[`j',2] = r(N) 
		
		putexcel set "Results/SupTable2b_`date'", replace
		putexcel B1 = "Lent"	
		putexcel A`j' = "N"	
		putexcel A2 = mat(B) , colnames
		}
	*Calculate how many votes / meetings / cusips we have*
	if 1 == 1 {
		use "SecLendingPOS_dates", clear
		gen trade_event_time = trade_date - trade_Recorddate
		keep if trade_event_time >=-40 & trade_event_time <=40
		xtset MeetingID trade_date
		gen gr_units = (units / L.units - 1)*100
		winsor2 gr_units, by(trade_event_time) cuts(5 95) suff(_w5)	
		keep if trade_event_time == 0 
		keep if gr_units_w5 != .
		distinct MeetingID cusip8 
		merge 1:m MeetingID using "Voting_combined" , gen(_temp)
		keep if _temp == 3 
		distinct MeetingID cusip8 
		
		
		use "SecLendingU2_dates", clear
		gen trade_event_time = trade_date - trade_Recorddate
		keep if trade_event_time >=-40 & trade_event_time <=40
		xtset MeetingID trade_date
		gen gr_available = (availableunits / L.availableunits - 1)*100
		winsor2 gr_available, by(trade_event_time) cuts(5 95) suff(_w5)	
		keep if trade_event_time == 0 
		merge 1:m MeetingID using "Voting_combined" , gen(_temp)
		keep if _temp == 3 
		distinct MeetingID cusip8 
		}
	}
	

	